﻿/*
#------------------------------------------------------------------------------
#-- Program Name:	[dbo].[spStg_Alerts]
#-- Purpose:		Updates Server Alerts in the Warehouse
#--	Last Update:	02/14/2017
#--					For a complete history - please review comments in Version
#--					Control.
#------------------------------------------------------------------------------
*/
CREATE PROCEDURE [dbo].[spStg_Alerts]
AS

SET NOCOUNT ON;

-------------------------------------------------------------------
--- Lock the staging tables
---
SELECT TOP 1 * FROM [dbo].[tblStg_Alerts] WITH (TABLOCK)

-------------------------------------------------------------------
--- Move staging data to Warehouse
---
UPDATE		whl		--- Update existing where counts have changed
SET			[alt_occurrence_count] = mon.[alt_occurrence_count]
FROM		[dbo].[tblStg_Alerts] mon
JOIN		[dbo].[tblWH_Alerts] whl ON mon.[alt_ssd_id] = whl.[alt_ssd_id]
			AND mon.[alt_name] = whl.[alt_name]
			AND mon.[alt_severity] = whl.[alt_severity]
			AND mon.[alt_message_id] = whl.[alt_message_id]
			AND mon.[alt_enabled] = whl.[alt_enabled]
			AND mon.[alt_log_date] = whl.[alt_log_date]
WHERE		NOT (
			mon.[alt_occurrence_count] = whl.[alt_occurrence_count]
			)


INSERT INTO [dbo].[tblWH_Alerts]
(
	[alt_ssd_id],
	[alt_name],
	[alt_severity],
	[alt_message_id],
	[alt_enabled],
	[alt_occurrence_count],
	[alt_log_date]
)
SELECT		mon.[alt_ssd_id],
			mon.[alt_name],
			mon.[alt_severity],
			mon.[alt_message_id],
			mon.[alt_enabled],
			mon.[alt_occurrence_count],
			mon.[alt_log_date]
FROM		[dbo].[tblStg_Alerts] mon
LEFT JOIN	[dbo].[tblWH_Alerts] whl ON mon.[alt_ssd_id] = whl.[alt_ssd_id]
			AND mon.[alt_name] = whl.[alt_name]
			AND mon.[alt_severity] = whl.[alt_severity]
			AND mon.[alt_message_id] = whl.[alt_message_id]
			AND mon.[alt_enabled] = whl.[alt_enabled]
			AND mon.[alt_log_date] = whl.[alt_log_date]
WHERE		whl.[alt_ssd_id] IS NULL
ORDER BY	mon.[alt_ssd_id],
			mon.[alt_severity],
			mon.[alt_message_id];

-------------------------------------------------------------------
--- Remove processed data
---
TRUNCATE TABLE [dbo].[tblStg_Alerts];

SET NOCOUNT OFF;